package cn.cds.withyou.base;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import cn.cds.withyou.lib.Constants;

public class SqliteOpenHelper extends BaseDBOpenHelper {


    public static final String DB_NAME = "WithYouBase.db";
    private static final int DB_VERSION = 1;
    private static SqliteOpenHelper instance;

    static public SqliteOpenHelper getInstance(Context context) {
        if (instance == null) {
            instance = new SqliteOpenHelper(context);
        }
        Log.e("OpenHelper", Application.getInstance().toString());
        return instance;
    }


    public SqliteOpenHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        createTables(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    private void createTables(SQLiteDatabase db) {
        // create user info table
        StringBuilder userSQL = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
                .append(Constants.UserInfo.TABLENAME).append(" (")
                .append(Constants._ID).append(" INTEGER PRIMARY KEY ASC AUTOINCREMENT, ")
                .append(Constants.UserInfo._PHONE_ITEM).append(" TEXT NOT NULL UNIQUE, ")
                .append(Constants.UserInfo._PASSWD_ITEM).append(" TEXT NOT NULL, ")
                .append(Constants.UserInfo._GENDER_ITEM).append(" TEXT, ")
                .append(Constants.UserInfo._INFO_ITEM).append(" TEXT, ")
                .append(Constants.UserInfo._NAME_ITEM).append(" TEXT, ")
                .append(Constants.UserInfo._QQ_ITEM).append(" TEXT, ")
                .append(Constants.UserInfo._COLLEGE_ITEM).append(" TEXT, ")
                .append(Constants.UserInfo._PROFESSION_ITEM).append(" TEXT, ")
                .append(Constants.UserInfo._TOKEN_ITEM).append(" TEXT);");
        db.execSQL(userSQL.toString());

        // create Game table
        StringBuilder gameSQL = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
                .append(Constants.Game.TABLENAME).append(" (")
                .append(Constants._ID).append(" INTEGER PRIMARY KEY ASC AUTOINCREMENT, ")
                .append(Constants.Game._GAME_NAME).append(" TEXT NOT NULL, ")
                .append(Constants.Game._SIGNUP_START_TIME).append(" LONG DEFAULT CURRENT_TIMESTAMP, ")
                .append(Constants.Game._SIGNUP_END_TIME).append(" LONG DEFAULT CURRENT_TIMESTAMP, ")
                .append(Constants.Game._GAME_START_TIME).append(" LONG DEFAULT CURRENT_TIMESTAMP, ")
                .append(Constants.Game._GAME_END_TIME).append(" LONG DEFAULT CURRENT_TIMESTAMP, ")
                .append(Constants.Game._GAME_CONTENT).append(" TEXT, ")
                .append(Constants.Game._GAME_PIC).append(" TEXT);");
        db.execSQL(gameSQL.toString());
        // create GamePlayer table
        StringBuilder gamePlayerSQL = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
                .append(Constants.GamePlayer.TABLENAME).append(" (")
                .append(Constants._ID).append(" INTEGER PRIMARY KEY ASC AUTOINCREMENT, ")
                .append(Constants.GamePlayer._GAME_ID).append(" TEXT NOT NULL, ")
                .append(Constants.GamePlayer._PHONE_ITEM).append(" TEXT NOT NULL, ")
                .append(Constants.GamePlayer._JOIN_TIME).append(" LONG DEFAULT CURRENT_TIMESTAMP);");
        db.execSQL(gamePlayerSQL.toString());

        // create Activity table
        StringBuilder activitySQL = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
                .append(Constants.Activity.TABLENAME).append(" (")
                .append(Constants._ID).append(" INTEGER PRIMARY KEY ASC AUTOINCREMENT, ")
                .append(Constants.Activity._ACTIVITY_NAME).append(" TEXT NOT NULL, ")
                .append(Constants.Activity._SIGNUP_START_TIME).append(" LONG DEFAULT CURRENT_TIMESTAMP, ")
                .append(Constants.Activity._SIGNUP_END_TIME).append(" LONG DEFAULT CURRENT_TIMESTAMP, ")
                .append(Constants.Activity._ACTIVITY_START_TIME).append(" LONG DEFAULT CURRENT_TIMESTAMP, ")
                .append(Constants.Activity._ACTIVITY_END_TIME).append(" LONG DEFAULT CURRENT_TIMESTAMP, ")
                .append(Constants.Activity._ACTIVITY_CONTENT).append(" TEXT, ")
                .append(Constants.Activity._ACTIVITY_COMMENT).append(" TEXT, ")
                .append(Constants.Activity._ACTIVITY_ADDRESS).append(" TEXT, ")
                .append(Constants.Activity._ACTIVITY_PIC).append(" TEXT);");
        db.execSQL(activitySQL.toString());
        // create ActivityJoiner table
        StringBuilder activityJoinerSQL = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
                .append(Constants.ActivityJoiner.TABLENAME).append(" (")
                .append(Constants._ID).append(" INTEGER PRIMARY KEY ASC AUTOINCREMENT, ")
                .append(Constants.ActivityJoiner._ACTIVITY_ID).append(" TEXT NOT NULL, ")
                .append(Constants.ActivityJoiner._PHONE_ITEM).append(" TEXT NOT NULL, ")
                .append(Constants.ActivityJoiner._JOIN_TIME).append(" LONG DEFAULT CURRENT_TIMESTAMP);");
        db.execSQL(activityJoinerSQL.toString());

        // create Feedback table
        StringBuilder feedbackSQL = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
                .append(Constants.FeedBack.TABLENAME).append(" (")
                .append(Constants._ID).append(" INTEGER PRIMARY KEY ASC AUTOINCREMENT, ")
                .append(Constants.FeedBack._PHONE_ITEM).append(" TEXT NOT NULL, ")
                .append(Constants.FeedBack._NAME_ITEM).append(" TEXT, ")
                .append(Constants.FeedBack._TIME_ITEM).append(" LONG DEFAULT CURRENT_TIMESTAMP, ")
                .append(Constants.FeedBack._CONTENT_ITEM).append(" TEXT);");
        db.execSQL(feedbackSQL.toString());

        // create Course table
        StringBuilder courseSQL = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
                .append(Constants.Course.TABLENAME).append(" (")
                .append(Constants._ID).append(" INTEGER PRIMARY KEY ASC AUTOINCREMENT, ")
                .append(Constants.Course._TITLE_ITEM).append(" TEXT NOT NULL, ")
                .append(Constants.Course._CONTENT_ITEM).append(" TEXT, ")
                .append(Constants.Course._PIC_ITEM).append(" TEXT, ")
                .append(Constants.Course._COURSE_URL).append(" TEXT NOT NULL, ")
                .append(Constants.Course._TIME_ITEM).append(" LONG DEFAULT CURRENT_TIMESTAMP);");
        db.execSQL(courseSQL.toString());

        // create Verify table
        StringBuilder verifySQL = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
                .append(Constants.Verify.TABLENAME).append(" (")
                .append(Constants._ID).append(" INTEGER PRIMARY KEY ASC AUTOINCREMENT, ")
                .append(Constants.Verify._PHONE_ITEM).append(" TEXT NOT NULL UNIQUE, ")
                .append(Constants.Verify._VERIFY_CODE).append(" TEXT NOT NULL, ")
                .append(Constants.Verify._VERIFY_TIME).append(" LONG DEFAULT CURRENT_TIMESTAMP);");
        db.execSQL(verifySQL.toString());

        // create Member Request table
        StringBuilder memberRequestSQL = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
                .append(Constants.MemberRequest.TABLE_NAME).append(" (")
                .append(Constants._ID).append(" INTEGER PRIMARY KEY ASC AUTOINCREMENT, ")
                .append(Constants.MemberRequest._NAME_ITEM).append(" TEXT NOT NULL, ")
                .append(Constants.MemberRequest._GENDER_ITEM).append(" TEXT NOT NULL, ")
                .append(Constants.MemberRequest._PHONE_ITEM).append(" TEXT NOT NULL UNIQUE, ")
                .append(Constants.MemberRequest._QQ_ITEM).append(" TEXT, ")
                .append(Constants.MemberRequest._EMAIL_ITEM).append(" TEXT, ")
                .append(Constants.MemberRequest._COLLEGE_ITEM).append(" TEXT, ")
                .append(Constants.MemberRequest._PROFESSION_ITEM).append(" TEXT, ")
                .append(Constants.MemberRequest._DEPARTMENT_ITEM).append(" TEXT, ")
                .append(Constants.MemberRequest._INTRODUCE_ITEM).append(" TEXT, ")
                .append(Constants.MemberRequest._FAVORITE_ITEM).append(" TEXT, ")
                .append(Constants.MemberRequest._PASSED_ITEM).append(" TEXT, ")
                .append(Constants.MemberRequest._CREATE_TIME).append(" LONG DEFAULT CURRENT_TIMESTAMP);");
        db.execSQL(memberRequestSQL.toString());

        // create VIP Request table
        StringBuilder vipRequestSQL = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
                .append(Constants.VIPRequest.TABLE_NAME).append(" (")
                .append(Constants._ID).append(" INTEGER PRIMARY KEY ASC AUTOINCREMENT, ")
                .append(Constants.VIPRequest._NAME_ITEM).append(" TEXT NOT NULL, ")
                .append(Constants.VIPRequest._GENDER_ITEM).append(" TEXT NOT NULL, ")
                .append(Constants.VIPRequest._PHONE_ITEM).append(" TEXT NOT NULL UNIQUE, ")
                .append(Constants.VIPRequest._QQ_ITEM).append(" TEXT, ")
                .append(Constants.VIPRequest._COLLEGE_ITEM).append(" TEXT, ")
                .append(Constants.VIPRequest._PROFESSION_ITEM).append(" TEXT, ")
                .append(Constants.VIPRequest._PASSED_ITEM).append(" TEXT, ")
                .append(Constants.VIPRequest._CREATE_TIME).append(" LONG DEFAULT CURRENT_TIMESTAMP);");
        db.execSQL(vipRequestSQL.toString());

        // create Arena table
        StringBuilder arenaSQL = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
                .append(Constants.Arena.TABLENAME).append(" (")
                .append(Constants._ID).append(" INTEGER PRIMARY KEY ASC AUTOINCREMENT, ")
                .append(Constants.Arena._NAME_ITEM).append(" TEXT NOT NULL, ")
                .append(Constants.Arena._PHONE_ITEM).append(" TEXT NOT NULL, ")
                .append(Constants.Arena._ADDRESS_ITEM).append(" TEXT NOT NULL, ")
                .append(Constants.Arena._PIC_ITEM).append(" TEXT NOT NULL, ")
                .append(Constants.Arena._COMMENT_ITEM).append(" TEXT, ")
                .append(Constants.Arena._SPACE_COUNT).append(" INT);");
        db.execSQL(arenaSQL.toString());

        // create Arena Booking table
        StringBuilder arenaBookSQL = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
                .append(Constants.ArenaBooking.TABLENAME).append(" (")
                .append(Constants._ID).append(" INTEGER PRIMARY KEY ASC AUTOINCREMENT, ")
                .append(Constants.ArenaBooking._ARENA_ID).append(" TEXT NOT NULL, ")
                .append(Constants.ArenaBooking._SPACE_ID).append(" TEXT NOT NULL, ")
                .append(Constants.ArenaBooking._START_TIME).append(" TEXT NOT NULL, ")
                .append(Constants.ArenaBooking._END_TIME).append(" TEXT NOT NULL, ")
                .append(Constants.ArenaBooking._USER_ID).append(" TEXT NOT NULL);");
        db.execSQL(arenaBookSQL.toString());

        if (BuildConfig.DEBUG) {
            db.execSQL("insert into UserInfo(passwd_item, phone_item) values('l', '1')");
        }
        // todo 创建表操作
    }
}
